use rusqlite::params;
use rusqlite::Connection;

#[derive(Debug, serde::Serialize)]
pub struct CodeInfo {
    id: i64,
    title: String,
    category_id: i64,
    category_name: String,
    code: String,
    sort: i64,
    gmt_create: i64,
    gmt_modified: i64,
}

#[tauri::command]
pub fn create_code_info(title: String, category_id: i64, category_name: String, code: String, sort: i64, gmt_create: i64, gmt_modified: i64) {
    let conn: Connection = Connection::open("./code_snippet.db").unwrap();
    conn.execute(
        "insert into code_info (title, category_id, category_name, code, sort, gmt_create, gmt_modified) values (?1, ?2, ?3, ?4, ?5, ?6, ?7)",
        ((&title), (&category_id), (&category_name), (&code), (&sort), (&gmt_create), (&gmt_modified)),
    )
    .unwrap();
}

#[tauri::command]
pub fn update_code_info(id: i64, title: String, category_id: i64, category_name: String, code: String, sort: i64, gmt_modified: i64) {
    let conn: Connection = Connection::open("./code_snippet.db").unwrap();
    conn.execute(
        "update code_info set title = ?1, category_id = ?2, category_name = ?3, code = ?4, sort = ?5, gmt_modified = ?6 where id = ?7",
        ((&title), (&category_id), (&category_name), (&code), (&sort), (&gmt_modified), (&id)),
    )
    .unwrap();
}

#[tauri::command]
pub fn delete_code_info(id: i64) {
    let conn: Connection = Connection::open("./code_snippet.db").unwrap();
    conn.execute("delete from code_info where id = ?1", (id,))
        .unwrap();
}

#[tauri::command]
pub fn view_code_info() -> CodeInfo {
    let conn: Connection = Connection::open("./code_snippet.db").unwrap();

    let mut stmt = conn
        .prepare("select id, title, code, sort, category_id, gmt_create, gmt_modified from code_info")
        .unwrap();
    let mut rows = stmt.query([]).unwrap();

    let row = rows.next().unwrap().unwrap();
    let c = CodeInfo {
        id: row.get(0).unwrap(),
        title: row.get(1).unwrap(),
        category_id: row.get(2).unwrap(),
        category_name: row.get(3).unwrap(),
        code: row.get(4).unwrap(),
        sort: row.get(5).unwrap(),
        gmt_create: row.get(6).unwrap(),
        gmt_modified: row.get(7).unwrap(),
    };
    c
}

#[tauri::command]
pub fn list_code_info_by_category(category_id: i64) -> Vec<CodeInfo> {
    let conn: Connection = Connection::open("./code_snippet.db").unwrap();
    let sql = "select * from code_info where category_id = ?1 order by sort asc";
    let mut stmt = conn.prepare(sql).unwrap();
    let mut rows = stmt.query(params![category_id]).unwrap();
    let mut list: Vec<CodeInfo> = Vec::new();
    while let Some(row) = rows.next().unwrap() {
        let c = CodeInfo {
            id: row.get(0).unwrap(),
            title: row.get(1).unwrap(),
            category_id: row.get(2).unwrap(),
            category_name: row.get(3).unwrap(),
            code: row.get(4).unwrap(),
            sort: row.get(5).unwrap(),
            gmt_create: row.get(6).unwrap(),
            gmt_modified: row.get(7).unwrap(),
        };
        list.push(c);
    }
     list
}

#[tauri::command]
pub fn list_code_info(keyword: String) -> Vec<CodeInfo> {
    let conn: Connection = Connection::open("./code_snippet.db").unwrap();
    let sql = "select * from code_info where code like ?1 or title like ?2 order by sort asc";

    let mut stmt = conn.prepare(sql).unwrap();

    // 准备用于查询的关键词，使用%进行模糊匹配
    let code_keyword = format!("%{}%", keyword);
    let title_keyword = format!("%{}%", keyword);
    let mut rows = stmt.query(params![code_keyword, title_keyword]).unwrap();

    let mut list: Vec<CodeInfo> = Vec::new();

    while let Some(row) = rows.next().unwrap() {
        let c = CodeInfo {
            id: row.get(0).unwrap(),
            title: row.get(1).unwrap(),
            category_id: row.get(2).unwrap(),
            category_name: row.get(3).unwrap(),
            code: row.get(4).unwrap(),
            sort: row.get(5).unwrap(),
            gmt_create: row.get(6).unwrap(),
            gmt_modified: row.get(7).unwrap(),
        };
        list.push(c);
    }
    list
}
